3 years analysis

R Markdown

Assets_by_Program <- read_excel("3 States Comparison_my work.xlsx", sheet = "Assets by Program")
Total_Withdrawals <- read_excel("3 States Comparison_my work.xlsx", sheet = "Total Withdrawals")
Funded_Accts <- read_excel("3 States Comparison_my work.xlsx", sheet = "Funded Accts")
Accts_with_Withdrawal <- read_excel("3 States Comparison_my work.xlsx", sheet = "Accts with Withdrawal")
Avg_Defferral_Rate <- read_excel("3 States Comparison_my work.xlsx", sheet = "Avg Defferral Rate")
Avg_Contrib_Amt <- read_excel("3 States Comparison_my work.xlsx", sheet = "Avg Contrib Amt")
Opt_Out_Rate <- read_excel("3 States Comparison_my work.xlsx", sheet = "Opt Out Rate")
Average_Funded_Account_Balance <- read_excel("3 States Comparison_my work.xlsx", sheet = "Average Funded Account Balance")
Registered_Employers <- read_excel("3 States Comparison_my work.xlsx", sheet = "Registered Employers")
Adding_Employee_Data <- read_excel("3 States Comparison_my work.xlsx", sheet = "Adding Employee Data")
Submitting_Payroll <- read_excel("3 States Comparison_my work.xlsx", sheet = "Submitting Payroll")

Including Plots

# Dynamic graph 
plot <-ggplot(Assets_by_Program, aes(x = Date)) +
  geom_line(aes(y = OregonSaves, color = "OregonSaves"), size = 1) +
  geom_line(aes(y = `IL Secure Choice`, color = "IL Secure Choice"), size = 1) +
  geom_line(aes(y = CalSavers, color = "CalSavers"), size = 1) +
  labs(
    title = "State Retirement Programs - Total Assets",
    x = "Date",
    y = "Total Assets",
    color = "Program"
  ) +
  scale_y_continuous(labels = scales::dollar_format(prefix = "$")) +
  theme_minimal() +
  theme(legend.position = "top")

ggplotly(plot)

Note that the echo = FALSE parameter was added to the code chunk to prevent printing of the R code that generated the plot.

library(tidyr)

your_data <- Assets_by_Program %>%
  arrange(Date) %>%
  mutate(
    OregonSaves_Growth = (OregonSaves / lag(OregonSaves)) - 1,
    `IL Secure Choice_Growth` = (`IL Secure Choice` / lag(`IL Secure Choice`)) - 1,
    CalSavers_Growth = (CalSavers / lag(CalSavers)) - 1
  )

growth_melted <- your_data %>%
  select(Date, OregonSaves, `IL Secure Choice`, CalSavers,
         OregonSaves_Growth, `IL Secure Choice_Growth`, CalSavers_Growth) %>%
  pivot_longer(cols = -Date, names_to = "Program", values_to = "Value")



# Filter for lines related to assets (exclude _Growth)
assets_data <- growth_melted %>%
  filter(!grepl("_Growth", Program))

assets_data2 <- growth_melted %>%
  filter(grepl("_Growth", Program)) %>%
  rename(Value2= Value)

data <- cbind(assets_data, assets_data2)

# Create the Plotly plot
plot_ly(data, x = ~Date, y = ~Value, color = ~Program, text = ~paste("Growth Rate: ", round(Value2, digits = 4)*100, "%")) %>%
  add_lines(line = list(width = 3)) %>%
  layout(
    title = "State Retirement Programs - Total Assets",
    xaxis = list(title = "Date"),
    yaxis = list(title = "Value"),
    hovermode = "x unified"
  )